package com.google.code.timetrail.backend;


import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class GameSave {
	
	private static final String DATABASE_LOCATION = "saves/savegames.db";
	
	private static final String SAVEGAME_TABLE_NAME = "SAVED_GAMES";
	
	private static final String SAVEGAME_TABLE_CREATOR = "CREATE TABLE IF NOT EXISTS " + SAVEGAME_TABLE_NAME + " (NAME TEXT NOT NULL ," + 
								" SAVE_DATA BLOB NOT NULL , CREATE_DATE DATETIME NOT NULL)";
	
	private static final String SAVEGAME_LOAD = "SELECT SAVE_DATA FROM " + SAVEGAME_TABLE_NAME + " WHERE ";
	
	private static final String SAVEGAME_SAVE = "INSERT INTO " + SAVEGAME_TABLE_NAME + " VALUES";
	
	private static final String SAVEGAME_DELETE = "DELETE FROM " + SAVEGAME_TABLE_NAME + " WHERE ";
	
	private static final String SAVEGAME_GET_ALL_ENTRIES = "SELECT NAME,CREATE_DATE FROM " + SAVEGAME_TABLE_NAME;
	
	private static final String SAVEGAME_UPDATE_ENTRY = "UPDATE " + SAVEGAME_TABLE_NAME + " SET ";
	
	private static final File  SAVE_GAME_FILE = new File(DATABASE_LOCATION);
	
	private static final SimpleDateFormat SQLDATE = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	public GameSave() throws ClassNotFoundException, IOException {
		if(!SAVE_GAME_FILE.exists()){
			SAVE_GAME_FILE.createNewFile();
		}
		Class.forName("org.sqlite.JDBC");
		Connection instanceCon = null;
		
		try{
			instanceCon = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_LOCATION);
			Statement createStatement = instanceCon.createStatement();
			createStatement.executeUpdate(SAVEGAME_TABLE_CREATOR);
		} catch (SQLException e){
			System.err.println(e);
		} finally {
			try{
		        if(instanceCon != null)
			          instanceCon.close();
			} catch(SQLException e) {
				System.err.println(e);
			}
		}
	}
	
	public void overwriteGame(String playerName, Control gameControl, String dateCreated) throws ClassNotFoundException, IOException {
		Date now = new Date();
		ByteArrayOutputStream updateByteStream = null;
		ObjectOutputStream updateObjectStream = null;
		Class.forName("org.sqlite.JDBC");
		Connection updateCon = null;
		
		updateByteStream = new ByteArrayOutputStream();
		updateObjectStream = new ObjectOutputStream(updateByteStream);
		updateObjectStream.writeObject(gameControl);
		updateObjectStream.close();

		try{
			updateCon = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_LOCATION);
			PreparedStatement updateStatement = updateCon.prepareStatement(SAVEGAME_UPDATE_ENTRY + "NAME='" + gameControl.getPlayer().getName() + "', "  
														+ "SAVE_DATA=?, " + "CREATE_DATE='" + SQLDATE.format(now) + "' WHERE NAME='" 
														+ playerName + "' AND CREATE_DATE='" + dateCreated + "'");
			updateStatement.setBytes(1,updateByteStream.toByteArray());
			updateStatement.executeUpdate();
		} catch (SQLException e){
				System.err.println(e);
		} finally {
			try{
				if(updateCon != null)
					updateCon.close();
			} catch(SQLException e) {
				System.err.println(e);
			}
		}
	}
	
	public void deleteGame(String playerName, String dateCreated) throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");
		Connection deleteCon = null;
		
		try{
			deleteCon = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_LOCATION);
			Statement deleteStatement = deleteCon.createStatement();
			deleteStatement.executeUpdate(SAVEGAME_DELETE + "NAME='" + playerName 
										+ "' AND CREATE_DATE='" + dateCreated + "'");
		} catch (SQLException e){
			System.err.println(e);
		} finally {
			try{
		        if(deleteCon != null)
			          deleteCon.close();
			} catch(SQLException e) {
				System.err.println(e);
			}
		}
	}
	
	public Control loadGame(String playerName, String dateCreated) throws ClassNotFoundException, IOException {
		ObjectInputStream loadObjectStream = null;
		ByteArrayInputStream loadByteStream = null;
		Class.forName("org.sqlite.JDBC");
		Connection loadCon = null;
		Control loadControl = null;
		
		try{
			loadCon = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_LOCATION);
			Statement loadStatement = loadCon.createStatement();
			loadStatement.setQueryTimeout(20);
			ResultSet loadSet = loadStatement.executeQuery(SAVEGAME_LOAD + "NAME='" 
								+ playerName + "' AND CREATE_DATE='" + dateCreated + "'");
			loadByteStream = new ByteArrayInputStream(loadSet.getBytes("SAVE_DATA"));
			loadObjectStream = new ObjectInputStream(loadByteStream);
			loadControl = (Control) loadObjectStream.readObject();
		} catch (SQLException e){
			System.err.println(e);
		} finally {
			try{
		        if(loadCon != null)
			          loadCon.close();
			} catch(SQLException e) {
				System.err.println(e);
			}
		}
		return loadControl;
	}
	
	public void saveGame(Control gameControl, String playerName) throws ClassNotFoundException,IOException {		
		Date now = new Date();
		ByteArrayOutputStream saveByteStream = null;
		ObjectOutputStream saveObjectStream = null;
		Class.forName("org.sqlite.JDBC");
		Connection saveCon = null;
		
		saveByteStream = new ByteArrayOutputStream();
		saveObjectStream = new ObjectOutputStream(saveByteStream);
		saveObjectStream.writeObject(gameControl);
		saveObjectStream.close();

		try{
			saveCon = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_LOCATION);
			PreparedStatement saveStatement = saveCon.prepareStatement(SAVEGAME_SAVE + "('" + playerName 
					+ "', ?, "+ "'" +SQLDATE.format(now)+ "'" +")");
			saveStatement.setBytes(1,saveByteStream.toByteArray());
			saveStatement.executeUpdate();
		} catch (SQLException e){
			System.err.println(e);
		} finally {
			try{
		        if(saveCon != null)
			          saveCon.close();
			} catch(SQLException e) {
				System.err.println(e);
			}
		}
	}
	
	public String[] listOfEntries() throws ClassNotFoundException{
		ArrayList<String> entryList = new ArrayList<String>();
		Class.forName("org.sqlite.JDBC");
		Connection listCon = null;
		
		try{
			listCon = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_LOCATION);
			Statement loadStatement = listCon.createStatement();
			loadStatement.setQueryTimeout(20);
			ResultSet loadSet = loadStatement.executeQuery(SAVEGAME_GET_ALL_ENTRIES);
			while(loadSet.next()){
				entryList.add(loadSet.getString("NAME")+ " " + loadSet.getString("CREATE_DATE"));
			}
		} catch (SQLException e){
			System.err.println(e);
		} finally {
			try{
		        if(listCon != null)
			          listCon.close();
			} catch(SQLException e) {
				System.err.println(e);
			}
		}
		return entryList.toArray(new String[entryList.size()]);
	}
	
}
